package com.hnust.dao;

import com.hnust.domain.*;
import com.hnust.domain.Class;
import com.hnust.domain.record.StudentDetailSubjectData;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 学生管理操作Dao
 * @author：xinrui
 * @date：2020/10/30
 */
@Mapper
@Repository
public interface TeacherClassDao {

    //根据老师id，考试id查询考试班级
    @Select("SELECT class.*\n" +
            "FROM tea_cla tc, test_class_group tcg, class\n" +
            "WHERE tc.tea_id = #{tea_id} AND tcg.testid = #{testid} AND tc.cla_id = tcg.cla_id AND tcg.cla_id = class.cla_id")
    @ResultType(Class.class)
    List<Class> queryClassByTestIDTeaId(String tea_id, String testid);

    //根据班级id查询专业名称
    @Select("SELECT major.name\n" +
            "            FROM major, class\n" +
            "            WHERE class.maj_id = major.maj_id  AND class.cla_id = #{cla_id}")
    @ResultType(String.class)
    String queryMajorNameByClassId(String cla_id);

    //根据班级号查询学生
    @Select("select * from student where cls_id=#{class_id} ")
    @ResultType(Student.class)
    List<Student> queryStudentsByClassId(String class_id);

//    根据教工号获取教工id
    @Select("select tea_number from teacher where tea_id =#{tea_id}")
    @ResultType(String.class)
    String queryTeaNumberByTeaId(String tea_id);



//    根据老师id查询班级
    @Select("select class.* from tea_course_group,class_course_group,class " +
            "where tea_course_group.cou_gro_id = class_course_group.cou_gro_id and class.cla_id = class_course_group.cla_id" +
            " and tea_course_group.tea_id=#{tea_id}")
    @ResultType(Class.class)
    List<Class> queryClassByTeaId(String tea_id);

//    查询老师所教课程
    @Select("select course.* from tea_course_group,course_group,course " +
            "where tea_course_group.cou_gro_id = course_group.cou_gro_id and " +
            "course.cou_id = course_group.cou_id " +
            "and tea_course_group.tea_id=#{tea_id}")
    @ResultType(Course.class)
    List<Course> queryCourseByTeaId(String tea_id);

//    查询某课程的做题数
    @Select("select count(*) from question_record,question " +
            "where question_record.que_id = question.que_id " +
            "and question.cou_id=#{course_id} and question_record.stu_id=#{stu_id}")
    @ResultType(Integer.class)
    Integer querySumCourse(String course_id,String stu_id);


//    查询课程正确个数
    @Select("select count(*) from question_record,question " +
            "where question_record.que_id = question.que_id " +
            "and question.cou_id=#{course_id} and question_record.`right`=1 " +
            "and question_record.stu_id=#{stu_id}")
    @ResultType(Integer.class)
    Integer queryRightCourse(String course_id,String stu_id);


    //    根据课程查询班级
    @Select("select cla_number from class,course_group,class_course_group where " +
            "class.cla_id=class_course_group.cla_id and " +
            "course_group.cou_gro_id=class_course_group.cou_gro_id" +
            " and course_group.cou_id=#{id}")
    @ResultType(Class.class)
    List<Class> queryAllClassByCourseGroupId(String id);

    //    根据班级查询课程
    @Select("select course.* from class,course_group,course,class_course_group where " +
            "class.cla_id=class_course_group.cla_id and " +
            "course_group.cou_gro_id=class_course_group.cou_gro_id and course.cou_id = course_group.cou_id " +
            " and class.cla_id=#{id}")
    @ResultType(Course.class)
    List<Course> queryAllCourseByCourseGroupId(String id);

    //根据班级号查询专业
    @Select("select major.name from major,class where class.cla_id=#{class_id} and major.maj_id=class.maj_id")
    @ResultType(String.class)
    String queryMajorByClassId(String class_id);


//    根据查询条件获取学生做题详细信息
    @SelectProvider(type = searchTeacherStuProvider.class,method = "searchTeacherStuSql")
    @ResultType(StudentDetailSubjectData.class)
     List<StudentDetailSubjectData> searchTeacherStu( String course_name, String stu_name, String class_name,String id);
    class searchTeacherStuProvider{
        public String searchTeacherStuSql(String course_name,String stu_name,String class_name,String id){
            String sql ="select stu_number as id,student.name,course.name as course, class.cla_number as classes," +
                    "major.name as profession from student,class,course_group,class_course_group,tea_course_group,course,major " +
                    "where student.cls_id = class.cla_id and tea_course_group.cou_gro_id = course_group.cou_gro_id and tea_course_group.tea_id=#{id}";
            /**
            student 表获取学号姓名班级，
            班级表用来联系学生表获取专业id，联系专业表获取专业名，
             然后和班级课程组相连得到cou_gro_id，然后班级课程组表和课程组表联系得到课程id，
             课程表用来和课程组表连接获取课程名称（name只有原表才有，引用的都是id作为外键
             */
            if(course_name!=null&&!course_name.equals("null")){

                sql=sql+" and course.name like concat('%',#{course_name},'%')";
            }
            if(class_name!=null&&!class_name.equals("null")){
                sql=sql +" and class.cla_number like concat('%',#{class_name},'%')";
            }
            if(stu_name!=null&&!stu_name.equals("null")){
                sql=sql+" and student.name like concat('%',#{stu_name},'%')";
            }
            sql= sql +" and class.cla_id=class_course_group.cla_id";//class表和班级课程组表连接
            sql =sql +" and course_group.cou_gro_id=class_course_group.cou_gro_id";
            //课程组表和班级课程组连接实现课程的查询
            sql= sql +" and course.cou_id=course_group.cou_id and major.maj_id=class.maj_id";
            return sql;
        }
    }

    //    查询某课程的做题数
    @Select("select count(*) from question_record,question,student,course " +
            "where question_record.que_id = question.que_id " +
            "and student.stu_id=question_record.stu_id and question.cou_id = course.cou_id " +
            "and course.name=#{name} and student.stu_number=#{stu_number}")
    @ResultType(Integer.class)
    Integer searchSumCourse(String name,String stu_number);


    //    查询课程正确个数
    @Select("select count(*) from question_record,question,student,course " +
            "where question_record.que_id = question.que_id " +
            "and student.stu_id=question_record.stu_id and question.cou_id = course.cou_id " +
            "and course.name=#{name} and question_record.`right`=1 " +
            "and student.stu_number=#{stu_number}")
    @ResultType(Integer.class)
    Integer searchRightCourse(String name,String stu_number);

////    查询正确geshu
//    @SelectProvider(type = searchTeacherStuRProvider.class,method = "searchTeacherStuSql")
//    @ResultType(Integer.class)
//    Integer searchTeacherStuRightTotal(String course,String stu_name,String classes);
//        class searchTeacherStuRProvider{
//            public String searchTeacherStuSql(String course,String stu_name,String classes){
//                String sql ="select count(*) from student,class,course_group,class_course_group,course,question,question_record " +
//                        "where student.cls_id = class.cla_id and question_record.`right`=1 and " +
//                        "question_record.que_id = question.que_id and question_record.stu_id = student.stu_id";
//                if(course!=null&&!course.equals("null")){
//                    sql=sql+" and course_group.cou_id =#{course}" +
//                            " and question.cou_id =#{course}";
//                }
//                if(classes!=null&&!classes.equals("null")){
//                    sql=sql +" and class.cla_id=#{classes}";
//                }
//                if(stu_name!=null&&!stu_name.equals("null")){
//                    sql=sql+" and student.name=#{stu_name}";
//                }
//                sql= sql +" and course.cou_id=course_group.cou_id";
//                sql= sql +" and class.cla_id=class_course_group.cla_id";//class表和班级课程组表连接
//                sql =sql +" and course_group.cou_gro_id=class_course_group.cou_gro_id";
//                //课程组表和班级课程组连接实现课程的查询
//                return sql;
//            }
//        }
////        查询做题总数
//    @SelectProvider(type = searchTeacherStuTProvider.class,method = "searchTeacherStuSql")
//    @ResultType(Integer.class)
//    Integer searchTeacherStuTotal(String course,String stu_name,String classes);
//    class searchTeacherStuTProvider{
//        public String searchTeacherStuSql(String course,String stu_name,String classes){
//            String sql ="select count(*) from student,class,course_group,class_course_group,course,question,question_record " +
//                    "where student.cls_id = class.cla_id and " +
//                    "question_record.que_id = question.que_id and question_record.stu_id = student.stu_id";
//            if(course!=null&&!course.equals("null")){
//                sql=sql+" and course_group.cou_id =#{course}" +
//                        " and question.cou_id =#{course}";
//            }
//            if(classes!=null&&!classes.equals("null")){
//                sql=sql +" and class.cla_id=#{classes}";
//            }
//            if(stu_name!=null&&!stu_name.equals("null")){
//                sql=sql+" and student.name=#{stu_name}";
//            }
//            sql= sql +" and course.cou_id=course_group.cou_id";
//            sql= sql +" and class.cla_id=class_course_group.cla_id";//class表和班级课程组表连接
//            sql =sql +" and course_group.cou_gro_id=class_course_group.cou_gro_id";
//            //课程组表和班级课程组连接实现课程的查询
//            return sql;
//        }
//    }


}